Imports ADODB
Imports System.Xml

Imports System.Diagnostics
Imports System.Data.OleDb
Imports System.Data.SqlClient.SqlCommand
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web


Public Class Data
    'PROGRAMADOR: WASCAR SANTIAGO
    'PROGRAMA: PORTAL INTEG - ADMINISTRATION SYSTEM
    'PAGINA O MODULO:DATA.VB
    'FECHA: 08/24/2004
    'DESCRIPCION: ESTE MODULO LO QUE HACE ES GENERA TODO TIPO DE CONNECION A LA BASE DE DATOS DESDE DATASET HASTA RECORDSET.
    'NINGUNA DE LAS VARIABLES DEBEN SER CAMBIADAS O BORRADAS YA QUE EL SISTEMA QUE HACE REFERENCIA A ESTE MODULO DEJARIA DE 
    'FUNCIONAR ADECUADAMENTE.
    Public sqlServer, UserName, Password, databasename As String
    Public CN As New ADODB.Connection
    Public CN_opts As New ADODB.Connection
    Public Rs As New ADODB.Recordset
    Public Rs_opts As New ADODB.Recordset
    Public SQLADP As SqlClient.SqlDataAdapter
    Public SQLCON As SqlClient.SqlConnection
    Public SQLCMD As SqlCommand
    Public DSdata As DataSet
    Public DVData As DataView
    Public V_opt As String
    Public INBOX As Integer
    Public SEND As Integer
    Public DRData As SqlDataReader
    Public ConnStr As String
    Public UseTrust As Boolean
    Public TrustConnStr As String
    Public StrConn As String
    Dim StrConn_opts As String
    Dim node As TreeNode
    Public CurrentProcessName() As String
    Public CurrentProcessID() As String
    Public CurrentProcessWindowTitle() As String
    Public msgstring As String
    Dim DSUPDHIST As DataSet
    Dim DVAFTER As New DataView
    Dim DVBefore As New DataView
    Dim IAFTER As Integer
    Dim IBEFORE As Integer
    Dim IROWS As Integer
    Public dsbefore_virtual As DataSet
    Public dsafter_virtual As DataSet
    Public ErrorText As String
    Function GetConfigData() As Boolean
        Dim XmlDoc As New XmlDocument()
        Dim doCrypt As New Encrypt
        XmlDoc.Load(AppDomain.CurrentDomain.SetupInformation.ConfigurationFile)
        For Each xElement As XmlElement In XmlDoc.DocumentElement
            If xElement.Name = "appSettings" Then
                For Each xNode As XmlNode In xElement.ChildNodes
                    Select Case xNode.Attributes(0).Value
                        Case "Database"
                            databasename = xNode.Attributes(1).Value
                        Case "DatabaseUser"
                            UserName = xNode.Attributes(1).Value
                        Case "DatabasePassword"
                            Password = doCrypt.decryption(xNode.Attributes(1).Value)
                        Case "DatabaseServer"
                            sqlServer = xNode.Attributes(1).Value
                        Case "UserTrustedConnection"
                            UseTrust = xNode.Attributes(1).Value
                    End Select
                Next
            End If
        Next
        TrustConnStr = "Data Source=" & sqlServer & ";Initial Catalog=" & databasename & ";Integrated Security=True"
        ConnStr = "user id=" & UserName & ";data source=" & sqlServer & ";persist security info=true;initial catalog=" & databasename & ";password=" & Password
        StrConn = "Data Source=" & sqlServer & ";database=" & databasename & ";uid=" & UserName & ";pwd=" & Password
        StrConn_opts = "Provider=SQLOLEDB;Data Source=" & sqlServer & ";database=" & databasename & ";uid=" & UserName & ";pwd=" & Password

        Return True
    End Function
    Public Sub ConnectionRS()
        GetConfigData()
        If CN.State = 1 Then
            CN.Close()
        End If
        CN.Open(StrConn)
    End Sub
    Public Sub ConnectionRS_opts()
        GetConfigData()
        If CN_opts.State = 1 Then
            CN_opts.Close()
        End If
        CN_opts.Open(StrConn_opts)
    End Sub
    Public Sub Rs_Data(ByVal ConsultaSQL As String)
        ConnectionRS()
        Rs.Open(ConsultaSQL, CN, 3, 3)
    End Sub
    Public Sub Rs_Data_opts(ByVal ConsultaSQL As String)
        ConnectionRS_opts()
        Rs_opts.Open(ConsultaSQL, CN_opts, 3, 3)
    End Sub
    Public Sub DS_Read(ByVal strsql As String, ByVal DSName As String)
        GetConfigData()
        If Me.UseTrust = True Then
            SQLCON = New SqlClient.SqlConnection(TrustConnStr)
        Else
            SQLCON = New SqlClient.SqlConnection(ConnStr)
        End If

        SQLCON.Open()
        SQLCMD = New SqlCommand(strsql)
        SQLCMD.Connection = SQLCON
        SQLADP = New SqlDataAdapter(SQLCMD)
        DSdata = New DataSet(DSName)
        SQLADP.Fill(DSdata)
        'HttpContext.Current.Response.Write(strsql)
    End Sub
    Public Sub DoCammand(ByVal strsql As String)
        GetConfigData()
        If Me.UseTrust = True Then
            SQLCON = New SqlClient.SqlConnection(TrustConnStr)
        Else
            SQLCON = New SqlClient.SqlConnection(ConnStr)
        End If
        SQLCON.Open()
        SQLCMD = New SqlCommand(strsql)
        SQLCMD.Connection = SQLCON
        SQLCMD.ExecuteNonQuery()
    End Sub
    Public Sub DS_StoreProcedure(ByVal strsql As String)
        GetConfigData()
        SQLCON = New SqlClient.SqlConnection(ConnStr)
        SQLCON.Open()
        SQLCMD = New SqlCommand(strsql)
        SQLCMD.Connection = SQLCON
        SQLCMD.ExecuteNonQuery()

        'HttpContext.Current.Response.Write(strsql)
    End Sub
    Public Sub BindingDropDown(ByVal DDName As ComboBox, _
                               ByVal DisplayValue As String, _
                               ByVal Value As String, ByVal strsql As String)
        DS_Read(strsql, "dsBinding")
        DVData = New DataView
        DVData.Table = DSdata.Tables(0)
        DDName.DataSource = DVData
        DDName.DisplayMember = DisplayValue
        DDName.ValueMember = Value

    End Sub
    Public Sub DS_REPORT(ByVal strsql As String, ByVal DSName As String, ByVal TABLENAME As String)
        GetConfigData()
        SQLCON = New SqlClient.SqlConnection(ConnStr)
        SQLCON.Open()
        SQLCMD = New SqlCommand(strsql)
        SQLCMD.Connection = SQLCON
        SQLADP = New SqlDataAdapter(SQLCMD)
        DSdata = New DataSet(DSName)
        SQLADP.Fill(DSdata, TABLENAME)
    End Sub
    'After = Despues
    'Before = Antes
    Public Sub DS_FILL_BEFORE(ByVal strsql As String, ByVal DSName As String)
        DS_Read(strsql, "dsbefore_virtual")
        DVBefore.Table = DSdata.Tables(0)
    End Sub
    Public Sub DS_FILL_AFTER(ByVal strsql As String, ByVal DSName As String)
        DS_Read(strsql, "dsafter_virtual")
        DVAFTER.Table = DSdata.Tables(0)
    End Sub

    Function AutoGenNumber(ByVal TABLE_NAME As String, ByVal FIELD_NAME As String, ByVal WHERE_CONDITION As String) As Integer
        Dim STR_AUTOGEN As String
        If WHERE_CONDITION = "" Then
            STR_AUTOGEN = "SELECT MAX (" & FIELD_NAME & ") + 1 AS IAPPCOUNT FROM " & TABLE_NAME
        Else
            STR_AUTOGEN = "SELECT MAX (" & FIELD_NAME & ") + 1 AS IAPPCOUNT FROM " & TABLE_NAME & " WHERE " & WHERE_CONDITION
        End If
        DS_Read(STR_AUTOGEN, "dsbefore_virtual")
        If IsDBNull(DSdata.Tables(0).Rows(0).Item(0)) = True Then
            Return 1
        Else
            Return DSdata.Tables(0).Rows(0).Item(0)
        End If
    End Function
    
End Class
